package com.hualan.dao.impl;

import com.hualan.bean.AssociateShop;
import com.hualan.bean.PanghuCommunity;
import com.hualan.bean.PanghuShop;
import com.hualan.dao.CommunityDao;
import com.hualan.utils.C3P0Util;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class CommunityDaoImpl implements CommunityDao {
    @Override
    public int addCommunity(PanghuCommunity community) {
        int row = 0;
        String sql = "insert into panghu_community(community_id, community_name, community_address, create_time, community_province, community_city, community_district, community_area_code, community_latitude, community_longitude, community_status, community_coordinates) values (?, ?, ?, ?, ? ,?, ?, ?, ?, ?, ?, ?)";
        Connection connection = C3P0Util.getConnection();
        try {
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.setInt(1, community.getCommunityId());
            statement.setString(2, community.getCommunityName());
            statement.setString(3, community.getCommunityAddress());
            statement.setDate(4, community.getCreateTime());
            statement.setString(5, community.getCommunityProvince());
            statement.setString(6, community.getCommunityCity());
            statement.setString(7, community.getCommunityDistrict());
            statement.setInt(8, community.getCommunityAreaCode());
            statement.setString(9, community.getCommunityLatitude());
            statement.setString(10, community.getCommunityLongitude());
            statement.setInt(11, community.getCommunityStatus());
            statement.setString(12, community.getCommunityCoordinates());
            row = statement.executeUpdate();
            connection.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return row;
    }

    @Override
    public int updateCommunity(PanghuCommunity community) {
        int row = C3P0Util.update("update panghu_community set community_name = ?, community_address = ?, community_province = ?, community_city = ?, community_district = ?, community_area_code = ?, community_latitude = ?, community_longitude = ?, community_status = ?, community_coordinates = ? where community_id = ?", community.getCommunityName(), community.getCommunityAddress(),community.getCommunityProvince(), community.getCommunityCity(), community.getCommunityDistrict(), community.getCommunityAreaCode(), community.getCommunityLatitude(), community.getCommunityLongitude(),community.getCommunityStatus(),community.getCommunityCoordinates(),community.getCommunityId());
        return row;
    }

    @Override
    public int deleteCommunityById(int id) {
        int row = 0;
        Connection connection = C3P0Util.getConnection();
        String sql = "delete from panghu_community where community_id = ?";
        try {
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.setInt(1,id);
            row = statement.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return row;
    }

    @Override
    public List<PanghuCommunity> selectCommunityByPage(int page, int size) {
        List<PanghuCommunity> communities = new ArrayList<>();
        String sql = "select * from panghu_community order by community_id desc limit ? , ?";
        try {
            PreparedStatement statement = C3P0Util.getConnection().prepareStatement(sql);
            statement.setInt(1, (page - 1) * size);
            statement.setInt(2, size);
            ResultSet resultSet = statement.executeQuery();
            while (resultSet.next()){
                int id = resultSet.getInt("community_id");
                String name = resultSet.getString("community_name");
                String address = resultSet.getString("community_address");
                Date date = resultSet.getDate("create_time");
                String province = resultSet.getString("community_province");
                String city = resultSet.getString("community_city");
                String district = resultSet.getString("community_district");
                int areacode = resultSet.getInt("community_area_code");
                String latitude = resultSet.getString("community_latitude");
                String longitude = resultSet.getString("community_longitude");
                int status = resultSet.getInt("community_status");
                String coordinates = resultSet.getString("community_coordinates");
                PanghuCommunity community = new PanghuCommunity(id,name,address,date,province,city,district,areacode,latitude,longitude,status,coordinates);
                community.setCommunityUserCount(0);
                community.setCommunityShopCount(new CommunityDaoImpl().AssociateCountShop(id));
                communities.add(community);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return communities;
    }

    @Override
    public List<PanghuCommunity> selectCommunityByLike(String message) {
        List<PanghuCommunity> communities = new ArrayList<>();
        String sql = "select * from panghu_community where community_name like ?";
        try {
            PreparedStatement statement = C3P0Util.getConnection().prepareStatement(sql);
            statement.setString(1, message+'%');
            ResultSet resultSet = statement.executeQuery();
            while (resultSet.next()){
                int id = resultSet.getInt("community_id");
                String name = resultSet.getString("community_name");
                String address = resultSet.getString("community_address");
                Date date = resultSet.getDate("create_time");
                String province = resultSet.getString("community_province");
                String city = resultSet.getString("community_city");
                String district = resultSet.getString("community_district");
                int areacode = resultSet.getInt("community_area_code");
                String latitude = resultSet.getString("community_latitude");
                String longitude = resultSet.getString("community_longitude");
                int status = resultSet.getInt("community_status");
                String coordinates = resultSet.getString("community_coordinates");
                PanghuCommunity community = new PanghuCommunity(id,name,address,date,province,city,district,areacode,latitude,longitude,status,coordinates);
                community.setCommunityUserCount(0);
                community.setCommunityShopCount(0);
                communities.add(community);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return communities;
    }

    @Override
    public List<AssociateShop> selectAssociateShop(int page, int size, int id) {
        List<AssociateShop> communities = new ArrayList<>();
        int i = 0;
        String sql = "select shop_name, shop_id, shop_sort, shop_address from panghu_shop join panghu_shop_community on shop_id = panghu_shop_id join panghu_community on community_id = panghu_community_id where community_id = ? order by shop_id desc limit ? , ?";
        try {
            PreparedStatement statement = C3P0Util.getConnection().prepareStatement(sql);
            statement.setInt(1, id);
            statement.setInt(2, (page - 1) * size);
            statement.setInt(3, size);
            ResultSet resultSet = statement.executeQuery();
            while (resultSet.next()){
                i++;
                int shop_id = resultSet.getInt("shop_id");
                String shop_name = resultSet.getString("shop_name");
                String shop_address = resultSet.getString("shop_address");
                int shop_sort = resultSet.getInt("shop_sort");
                AssociateShop associateShop = new AssociateShop(shop_name,shop_id,shop_sort,shop_address);
                associateShop.setShop_number(i);
                associateShop.setDingdan_number(0);
                communities.add(associateShop);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return communities;
    }

    @Override
    public List<AssociateShop> selectAssociateLikeShop(int id, String message, int page, int size) {
        List<AssociateShop> list = new ArrayList<>();
        int i = 0;
        String sql = "select shop_name, shop_id, shop_sort, shop_address from panghu_shop join panghu_shop_community on shop_id = panghu_shop_id join panghu_community on community_id = panghu_community_id where community_id = ? and shop_name = ? limit ? , ?";
        Connection connection = C3P0Util.getConnection();
        try {
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.setInt(1, id);
            statement.setString(2, message);
            statement.setInt(3, (page - 1) * size);
            statement.setInt(4,  size);
            ResultSet resultSet = statement.executeQuery();
            while (resultSet.next()){
                i++;
                int shop_id = resultSet.getInt("shop_id");
                String shop_name = resultSet.getString("shop_name");
                String shop_address = resultSet.getString("shop_address");
                int shop_sort = resultSet.getInt("shop_sort");
                AssociateShop associateShop = new AssociateShop(shop_name,shop_id,shop_sort,shop_address);
                associateShop.setShop_number(i);
                associateShop.setDingdan_number(0);
                list.add(associateShop);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return list;
    }

    //查看关联门店数量
    public int AssociateCountShop(int id){
        int row = 0;
        String sql = "select count(shop_id) from panghu_shop join panghu_shop_community on shop_id = panghu_shop_id join panghu_community on community_id = panghu_community_id where community_id = ?";
        Map<String,Object> map = new HashMap<>();
        Connection connection = C3P0Util.getConnection();
        try {
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.setInt(1, id);
            ResultSet resultSet = statement.executeQuery();
            if (resultSet.next()){
                return resultSet.getInt("count(shop_id)");
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return row;
    };

    @Override
    public int deleteAssociateShop(int id) {
        int row = 0;
        String sql1 = "delete from panghu_shop where shop_id = ?";
        String sql2 = "delete from panghu_shop_community where panghu_shop_id = ?";
        Connection connection = C3P0Util.getConnection();
        try {
            PreparedStatement statement1 = connection.prepareStatement(sql1);
            PreparedStatement statement2 = connection.prepareStatement(sql2);
            statement1.setInt(1, id);
            statement2.setInt(1, id);
            row = statement1.executeUpdate() + statement2.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return row;
    }

    @Override
    public List<PanghuShop> selectAssociateAddShop() {
        List<PanghuShop> list = new ArrayList<>();
        String sql = "select * from panghu_shop";
        Connection connection = C3P0Util.getConnection();
        try {
            PreparedStatement statement = connection.prepareStatement(sql);
            ResultSet resultSet = statement.executeQuery();
            while (resultSet.next()){
                int shopadd_id = resultSet.getInt("shop_id");
                String shopadd_name = resultSet.getString("shop_name");
                String shopadd_address = resultSet.getString("shop_address");
                int shopadd_sort = resultSet.getInt("shop_sort");
                PanghuShop shop = new PanghuShop(shopadd_id,shopadd_name,shopadd_address,shopadd_sort);
                shop.setShopOrderCount(0);
                list.add(shop);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return list;
    }

    @Override
    public List<PanghuShop> selectAssociateLikeShop(String message) {
        List<PanghuShop> list = new ArrayList<>();
        String sql = "select * from panghu_shop where shop_name like ?";
        Connection connection = C3P0Util.getConnection();
        try {
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.setString(1, message+'%');
            ResultSet resultSet = statement.executeQuery();
            while (resultSet.next()){
                int shopadd_id = resultSet.getInt("shop_id");
                String shopadd_name = resultSet.getString("shop_name");
                String shopadd_address = resultSet.getString("shop_address");
                int shopadd_sort = resultSet.getInt("shop_sort");
                PanghuShop shop = new PanghuShop(shopadd_id,shopadd_name,shopadd_address,shopadd_sort);
                shop.setShopOrderCount(0);
                list.add(shop);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return list;
    }

    @Override
    public int insertAssociateShop(int shopId, int communityId, int shopSort) {
        int row = 0;
        Connection connection = C3P0Util.getConnection();
        String sql = "insert into panghu_shop_community (panghu_shop_id, panghu_community_id, shop_community_sort) values(?, ? ,?)";
        try {
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.setInt(1, shopId);
            statement.setInt(2, communityId);
            statement.setInt(3, shopSort);
            row = statement.executeUpdate();

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return row;
    }
}
